package kr.hkit.powerjava.chapter26.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.hkit.powerjava.chapter26.common.DbUtil;
import kr.hkit.powerjava.chapter26.dto.Department;

public class DepartmentDAO_1 implements DaoInterface<Department>{
	private Connection connection;
	private PreparedStatement pstmt;
	
	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	public DepartmentDAO_1(Connection connection) {
		super();
		this.connection = connection;
	}

	@Override
	public void insertItem(Department item) throws SQLException {
		String sql="insert into department values(?,?,?)";
		pstmt=connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptno());
		pstmt.setString(2, item.getDeptname());
		pstmt.setInt(3, item.getFloor());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}
	@Override
	public void deleteItemByNo(Department item) throws SQLException {
		String sql="delete from department where=deptno?";
		pstmt=connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptno());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}
	@Override
	public void updateItemByNo(Department item) throws SQLException {
		String sql="update department set deptname=?,floor=? where deptno=?";
		pstmt=connection.prepareStatement(sql);
		pstmt.setString(1, item.getDeptname());
		pstmt.setInt(2, item.getFloor());
		pstmt.setInt(3, item.getDeptno());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}

	@Override
	public ArrayList<Department> listItems() throws SQLException {
		ArrayList<Department> list= new ArrayList<>();
		String sql="select deptno,deptname,floor from department";
		pstmt=connection.prepareStatement(sql);
		ResultSet rs= pstmt.executeQuery();
		while(rs.next()){
			list.add(new Department(rs.getInt("deptno"),
									rs.getString("deptname"),
									rs.getInt("floor")));
		}
		return list;
	}

	@Override
	public Department searchItem(Department item) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

}
